package cn.buk.tms.dao;

import cn.buk.tms.dto.CarrierStatisticsDto;
import cn.buk.tms.dto.PnrDzSummary;
import cn.buk.tms.dto.PnrSummary;
import cn.buk.tms.dto.ReportFlightChangeDto;
import cn.buk.tms.dto.ReportFlightRefundDto;
import cn.buk.tms.dto.ReportFlightTicketDto;
import cn.buk.tms.dto.ReportHotelDto;
import cn.buk.tms.dto.ReportStatSubItemDto;
import cn.buk.tms.dto.ReportStatisticDto;
import cn.buk.tms.dto.ReportTrainDto;
import cn.buk.tms.dto.ReportVasDto;
import cn.buk.tms.dto.SupplierDto;
import cn.buk.tms.entity.misc.Customer;
import cn.buk.tms.entity.misc.PaymentMethod;
import cn.buk.tms.entity.misc.Supplier;
import cn.buk.tms.common.dto.CustomerDto;
import cn.buk.tms.common.dto.PaymentMethodDto;
import cn.buk.tms.factory.CustomerDtoFactory;
import cn.buk.common.util.DateUtil;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jakarta.persistence.Query;
import jakarta.transaction.Transactional;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Repository;

import static cn.buk.tms.common.constant.TmsOrderConstant.BILL_TYPE_FLIGHT;
import static cn.buk.tms.common.constant.TmsOrderConstant.BILL_TYPE_FLIGHT_CHANGE;


/**
 * @author yfdai
 */
@Repository
@Transactional
public class ReportDaoImpl extends AbstractDao implements ReportDao {

  private static final Logger logger = LogManager.getLogger(ReportDaoImpl.class);

  @SuppressWarnings("unchecked")
  @Override
  public List<CarrierStatisticsDto> getCarrierStatistics(int enterpriseId,
                                                         Date fromDate, Date toDate) {
    String jql = "select o.balanceCode, sum(o.price), sum(o.tax), sum(o.accountRecv), sum(o.profit) from BillFlightTicket o "
            + " where o.saleDate >= :fromDate and o.saleDate <= :toDate and o.enterpriseId = :enterpriseId"
            + " group by o.balanceCode"
            + " order by o.balanceCode";
    List<CarrierStatisticsDto> dtos = new ArrayList<>();


    Query query = em.createQuery(jql)
            .setParameter("fromDate", fromDate)
            .setParameter("toDate", toDate)
            .setParameter("enterpriseId", enterpriseId);

    List<Object[]> results = query.getResultList();


    for (Object[] result : results) {
      CarrierStatisticsDto dto = new CarrierStatisticsDto();
      dtos.add(dto);

      dto.setBalanceCode((String) result[0]);
      dto.setPrice(((Number) result[1]).doubleValue());
      dto.setTax(((Number) result[2]).doubleValue());
      dto.setReceipt(((Number) result[3]).doubleValue());
      dto.setProfit(((Number) result[4]).doubleValue());
    }


    return dtos;
  }

  @Override
  public List<PnrSummary> getPnrSummary(int enterpriseId) {
    List<PnrSummary> lists = new ArrayList<>();

    List<Object[]> results = em.createQuery("select o.etermUsername, count(o) as pnrCount from Pnr o "
                    + " where o.enterpriseId = :enterpriseId"
                    + " group by o.etermUsername order by pnrCount desc", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .getResultList();

    for (Object[] result : results) {

      if (result[0] == null) {
        continue;
      }

      String etermUsername = (String) result[0];
      etermUsername = etermUsername.trim();
      if (etermUsername.length() == 0) {
        continue;
      }

      PnrSummary dto = new PnrSummary();
      lists.add(dto);

      dto.setEtermUsername(etermUsername);

      int pnrCount = ((Number) result[1]).intValue();
      dto.setPnrCount(pnrCount);
    }

    return lists;
  }

  @Override
  public List<PnrDzSummary> getPnrDzSummary(int enterpriseId, String etermUsername) {
    List<PnrDzSummary> lists = new ArrayList<>();

    List<Object[]> results = em.createQuery("select o.dzStatus, o.etdzOffice, count(o) as pnrCount from Pnr o "
                    + " where o.enterpriseId = :enterpriseId"
                    + " and o.etermUsername = :etermUsername"
                    + " group by o.dzStatus, o.etdzOffice order by pnrCount desc", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("etermUsername", etermUsername)
            .getResultList();

    for (Object[] result : results) {

      if (result[0] == null) {
        continue;
      }

      PnrDzSummary dto = new PnrDzSummary();
      lists.add(dto);

      dto.setDzStatus(((Number) result[0]).intValue());

      if (result[1] == null) {
        dto.setEtdzOffice("");
      } else {
        dto.setEtdzOffice((String) result[1]);
      }

      int pnrCount = ((Number) result[2]).intValue();
      dto.setPnrCount(pnrCount);
    }

    return lists;
  }

  @Override
  public List<ReportFlightTicketDto> searchFlightTickets(int enterpriseId, Date beginDate, Date endDate,
                                                         final int supplierId) {
    List<ReportFlightTicketDto> results = null;

    if (supplierId < 1) {
      results = em.createNamedQuery("BillFlightTicket.searchMonthlyTickets", ReportFlightTicketDto.class)
              .setParameter("enterpriseId", enterpriseId)
              .setParameter("beginDate", beginDate)
              .setParameter("endDate", endDate)
              .getResultList();
    } else {
      results = em.createNamedQuery("BillFlightTicket.searchMonthlyTicketsBySupplier", ReportFlightTicketDto.class)
              .setParameter("enterpriseId", enterpriseId)
              .setParameter("beginDate", beginDate)
              .setParameter("endDate", endDate)
              .setParameter("supplierId", supplierId)
              .getResultList();
    }


    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<ReportFlightRefundDto> searchFlightRefunds(int enterpriseId, Date beginDate, Date endDate) {
    List<ReportFlightRefundDto> results = null;

    results = em.createNamedQuery("BillFlightRefund.searchMonthly", ReportFlightRefundDto.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();


    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<ReportFlightChangeDto> searchFlightChanges(int enterpriseId, Date beginDate, Date endDate) {
    List<ReportFlightChangeDto> results = null;

    results = em.createNamedQuery("BillChangeOrder.searchMonthly", ReportFlightChangeDto.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();


    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<ReportVasDto> searchVasOrders(int enterpriseId, Date beginDate, Date endDate) {
    List<ReportVasDto> results = null;

    results = em.createNamedQuery("BillVasOrder.searchMonthly", ReportVasDto.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();


    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<ReportHotelDto> searchHotelOrders(int enterpriseId, Date beginDate, Date endDate) {

    return em.createNamedQuery("BillHotelBooking.searchMonthly", ReportHotelDto.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();

  }

  @Override
  public List<ReportTrainDto> searchTrainOrders(int enterpriseId, Date beginDate, Date endDate) {

    return em.createNamedQuery("BillTrainTicket.searchMonthly", ReportTrainDto.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();

  }


  /**
   * 按客户进行月统计，包括所有账单，区分已支付和未支付
   *
   * @param enterpriseId 企业id
   * @param beginDate    开始日期
   * @param endDate      截止日期
   * @return
   */
  @Override
  public List<ReportStatisticDto> statMonthly(int enterpriseId, LocalDate beginDate, LocalDate endDate) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats = em.createQuery("select b.customerId, b.bePaid, count(b) as billCount, " +
                    "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit " +
                    "from BillInfo b  " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate " +
                    " group by b.customerId, b.bePaid", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", DateUtil.createDate(beginDate))
            .setParameter("endDate", DateUtil.createDate(endDate))
            .getResultList();


    for (Object[] stat : stats) {
      ReportStatisticDto dto = new ReportStatisticDto();

      Integer customerId = (Integer) stat[0];
      if (customerId == null) {
        // 散客
        dto.setCustomer(CustomerDtoFactory.createByCustomer(null));
      } else {
        Customer customer = em.find(Customer.class, customerId);
        dto.setCustomer(CustomerDtoFactory.createByCustomer(customer));
      }

      String paid = (String) stat[1];

      int count = ((Number) stat[2]).intValue();
      dto.setCount(count);

      double amount = ((Number) stat[3]).doubleValue();
      dto.setTotalAmount(amount);
      if (paid == null || "0".equalsIgnoreCase(paid)) {
        // 未支付
        dto.setUnpaidTotalAmount(amount);
      } else {
        // 已支付
        dto.setPaidTotalAmount(amount);
      }

      double cost = ((Number) stat[4]).doubleValue();
      dto.setTotalCost(cost);
      if (paid == null || "0".equalsIgnoreCase(paid)) {
        // 未支付
        dto.setUnpaidTotalCost(cost);
      } else {
        // 已支付
        dto.setPaidTotalCost(cost);
      }

      double profit = ((Number) stat[5]).doubleValue();
      dto.setTotalProfit(profit);
      if (paid == null || "0".equalsIgnoreCase(paid)) {
        // 未支付
        dto.setUnpaidTotalProfit(profit);
      } else {
        // 已支付
        dto.setPaidTotalProfit(profit);
      }

      results.add(dto);
    }

    return results;
  }

  @Override
  public List<ReportStatisticDto> statBusinessCustomer(int enterpriseId, LocalDate beginDate, LocalDate endDate) {
    List<ReportStatisticDto> results = new ArrayList<>();


    List<Object[]> stats = em.createQuery("select distinct b.customerId, b.customerCode from BillInfo b  " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", DateUtil.createDate(beginDate))
            .setParameter("endDate", DateUtil.createDate(endDate))
            .getResultList();


    for (Object[] stat : stats) {
      ReportStatisticDto dto = new ReportStatisticDto();

      Integer customerId = (Integer) stat[0];
      if (customerId != null) {
        Customer customer = em.find(Customer.class, customerId);
        dto.setCustomer(CustomerDtoFactory.createByCustomer(customer));
      }

      String customerCode = (String) stat[1];
      dto.setCustomerCode(customerCode);

      results.add(dto);
    }

    return results;
  }

  @Override
  public List<ReportStatisticDto> statAmountBySupplier(final int enterpriseId, final int dateType, Date beginDate, Date endDate, List<Integer> allowedCustomerIds) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats;
    if (dateType == 0) {
      //以账单日期为统计基准
      stats = em.createQuery("select b.supplier, b.billType, b.paymentStatus, count(b) as billCount, " +
                      "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit, b.customerId " +
                      "from BillInfo b " +
                      " where b.enterpriseId = :enterpriseId " +
                      "and b.opDate >= :beginDate and b.opDate <= :endDate " +
                      " group by b.supplier, b.billType, b.paymentStatus, b.customerId", Object[].class)
              .setParameter("enterpriseId", enterpriseId)
              .setParameter("beginDate", beginDate)
              .setParameter("endDate", endDate)
              .getResultList();
    } else {
      //以审核日期
      stats = em.createQuery("select b.supplier, b.billType, b.paymentStatus, count(b) as billCount, " +
                      "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit, b.customerId " +
                      "from BillInfo b " +
                      " where b.enterpriseId = :enterpriseId " +
                      " and b.audited = '1' " +
                      "and b.auditDate >= :beginDate and b.auditDate <= :endDate " +
                      " group by b.supplier, b.billType, b.paymentStatus, b.customerId ", Object[].class)
              .setParameter("enterpriseId", enterpriseId)
              .setParameter("beginDate", beginDate)
              .setParameter("endDate", endDate)
              .getResultList();
    }

    for (Object[] stat : stats) {
      Supplier supplier = (Supplier) stat[0];
      final String billType0 = (String) stat[1];
      final int paymentStatus = stat[2] == null ? 0 : (int) stat[2];
      final int count = ((Number) stat[3]).intValue();
      final double amount = ((Number) stat[4]).doubleValue();
      final double cost = ((Number) stat[5]).doubleValue();
      final double profit = ((Number) stat[6]).doubleValue();
      final int customerId = stat[7] == null ? 0 : (int) stat[7];

      if (allowedCustomerIds != null && !allowedCustomerIds.contains(customerId)) {
        continue;
      }

      ReportStatisticDto dto = null;
      //查看该供应商是否已存在
      for (ReportStatisticDto dto0 : results) {
        if (dto0.getSupplier().getId() == supplier.getId()) {
          dto = dto0;
          break;
        }
      }
      //不存在，则新增加一个供应商
      if (dto == null) {
        dto = new ReportStatisticDto();
        dto.setSupplier(SupplierDto.createBySupplier(supplier));

        results.add(dto);
      }


      dto.setCount(count + dto.getCount());
      dto.setTotalAmount(amount + dto.getTotalAmount());
      dto.setTotalCost(cost + dto.getTotalCost());
      if (paymentStatus == 0) {
        dto.setUnpaidTotalCost(cost + dto.getUnpaidTotalCost());
      }
      dto.setTotalProfit(profit + dto.getTotalProfit());

      //分账单类型进行子项目统计
      ReportStatSubItemDto itemDto = null;
      //机票和改签合并为正常票
      String billType = billType0;
      if (billType.equalsIgnoreCase(BILL_TYPE_FLIGHT_CHANGE)) {
        billType = BILL_TYPE_FLIGHT;
      }
      for (ReportStatSubItemDto item : dto.getItems()) {
        if (item.getBillType().equalsIgnoreCase(billType)) {
          itemDto = item;
          break;
        }
      }
      if (itemDto == null) {
        itemDto = new ReportStatSubItemDto();
        itemDto.setBillType(billType);
        if (billType.equalsIgnoreCase(BILL_TYPE_FLIGHT)) {
          itemDto.setBillTypeName("正常票");
        }
        dto.getItems().add(itemDto);
      }
      itemDto.setCount(count + itemDto.getCount());
      itemDto.setTotalAmount(amount + itemDto.getTotalAmount());
      itemDto.setTotalCost(cost + itemDto.getTotalCost());

      itemDto.setTotalProfit(profit + itemDto.getTotalProfit());

    }

    return results;
  }


  @Override
  public List<ReportStatisticDto> statAmountByPaymentMethod(int enterpriseId, int dateType, Date beginDate, Date endDate, List<Integer> allowedCustomerIds) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats;
    if (dateType == 0) {
      //以账单日期为统计基准
      stats = em.createQuery("select b.paymentMethod, b.billType, b.paymentStatus, count(b) as billCount, " +
                      "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit, b.customerId " +
                      "from BillInfo b " +
                      " where b.enterpriseId = :enterpriseId " +
                      "and b.opDate >= :beginDate and b.opDate <= :endDate " +
                      " group by b.paymentMethod, b.billType, b.paymentStatus, b.customerId " +
                      " order by totalAmount desc", Object[].class)
              .setParameter("enterpriseId", enterpriseId)
              .setParameter("beginDate", beginDate)
              .setParameter("endDate", endDate)
              .getResultList();
    } else {
      //以审核日期
      stats = em.createQuery("select b.paymentMethod, b.billType, b.paymentStatus, count(b) as billCount, " +
                      "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit, b.customerId " +
                      "from BillInfo b " +
                      " where b.enterpriseId = :enterpriseId " +
                      " and b.audited = '1' " +
                      "and b.auditDate >= :beginDate and b.auditDate <= :endDate " +
                      " group by b.paymentMethod, b.billType, b.paymentStatus, b.customerId " +
                      " order by totalAmount desc", Object[].class)
              .setParameter("enterpriseId", enterpriseId)
              .setParameter("beginDate", beginDate)
              .setParameter("endDate", endDate)
              .getResultList();
    }

    for (Object[] stat : stats) {
      PaymentMethod pm = (PaymentMethod) stat[0];
      final String billType0 = (String) stat[1];
      final int paymentStatus = stat[2] == null ? 0 : (int) stat[2];
      final int count = ((Number) stat[3]).intValue();
      final double amount = ((Number) stat[4]).doubleValue();
      final double cost = ((Number) stat[5]).doubleValue();
      final double profit = ((Number) stat[6]).doubleValue();
      final int customerId = stat[7] == null ? 0 : (Integer) stat[7];

      if (allowedCustomerIds != null && !allowedCustomerIds.contains(customerId)) {
        continue;
      }

      ReportStatisticDto dto = null;
      for (ReportStatisticDto dto0 : results) {
        if (dto0.getPayMethod().getId() == pm.getId()) {
          dto = dto0;
          break;
        }
      }
      if (dto == null) {
        dto = new ReportStatisticDto();
        dto.setPayMethod(PaymentMethodDto.create(pm.getId(), pm.getName()));

        results.add(dto);
      }


      dto.setCount(dto.getCount() + count);
      dto.setTotalAmount(amount + dto.getTotalAmount());
      dto.setTotalCost(cost + dto.getTotalCost());
      if (paymentStatus == 0) {
        dto.setUnpaidTotalCost(cost + dto.getUnpaidTotalCost());
      }
      dto.setTotalProfit(profit + dto.getTotalProfit());


      //分账单类型进行子项目统计
      ReportStatSubItemDto itemDto = null;
      //机票和改签合并为正常票
      String billType = billType0;
      if (billType.equalsIgnoreCase(BILL_TYPE_FLIGHT_CHANGE)) {
        billType = BILL_TYPE_FLIGHT;
      }
      for (ReportStatSubItemDto item : dto.getItems()) {
        if (item.getBillType().equalsIgnoreCase(billType)) {
          itemDto = item;
          break;
        }
      }
      if (itemDto == null) {
        itemDto = new ReportStatSubItemDto();
        itemDto.setBillType(billType);
        if (billType.equalsIgnoreCase(BILL_TYPE_FLIGHT)) {
          itemDto.setBillTypeName("正常票");
        }
        dto.getItems().add(itemDto);
      }
      itemDto.setCount(count + itemDto.getCount());
      itemDto.setTotalAmount(amount + itemDto.getTotalAmount());
      itemDto.setTotalCost(cost + itemDto.getTotalCost());

      itemDto.setTotalProfit(profit + itemDto.getTotalProfit());
    }

    return results;
  }

  @Override
  public List<ReportStatisticDto> statFlightMonthSaleByOp1(int enterpriseId, Date beginDate, Date endDate, List<Integer> allowedCustomerIds) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats = em.createQuery("select b.op1, b.billType," +
                    " count(b) as billCount, " +
                    "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit, b.customerId " +
                    "from BillInfo b " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate " +
                    " group by b.op1, b.billType, b.customerId " +
                    " order by b.op1, b.billType", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();

    String prevOp1 = null, op1 = null;

    ReportStatisticDto dto = null;

    for (Object[] stat : stats) {
      final int customerId = stat[6] == null ? 0 : (int) stat[6];
      if (allowedCustomerIds != null && !allowedCustomerIds.contains(customerId)) {
        continue;
      }

      op1 = (String) stat[0];
      if (!op1.equalsIgnoreCase(prevOp1)) {
        dto = new ReportStatisticDto();
      }
      processReportStatItem(0, op1, prevOp1, dto, stat, results);
      if (!op1.equalsIgnoreCase(prevOp1)) {
        prevOp1 = op1;
      }
    }

    return results;
  }

  private void processReportStatItem(final int opType, final String op, final String prevOp, ReportStatisticDto dto,
                                     Object[] stat, List<ReportStatisticDto> results) {
    if (!op.equalsIgnoreCase(prevOp)) {
      // new one

      if (opType == 0) {
        dto.setOp1(op);
      } else if (opType == 1) {
        dto.setOp2(op);
      }


      int count = ((Number) stat[2]).intValue();
      dto.setCount(count);

      double amount = ((Number) stat[3]).doubleValue();
      dto.setTotalAmount(amount);

      double cost = ((Number) stat[4]).doubleValue();
      dto.setTotalCost(cost);

      double profit = ((Number) stat[5]).doubleValue();
      dto.setTotalProfit(profit);

      results.add(dto);
    } else {
      int count = ((Number) stat[2]).intValue();
      dto.setCount(count + dto.getCount());

      double amount = ((Number) stat[3]).doubleValue();
      dto.setTotalAmount(amount + dto.getTotalAmount());

      double cost = ((Number) stat[4]).doubleValue();
      dto.setTotalCost(cost + dto.getTotalCost());

      double profit = ((Number) stat[5]).doubleValue();
      dto.setTotalProfit(profit + dto.getTotalProfit());
    }

    ReportStatSubItemDto oldItem = null;
    final String billType = (String) stat[1];
    if (!dto.getItems().isEmpty()) {
      for (ReportStatSubItemDto item : dto.getItems()) {
        if (billType.equalsIgnoreCase(item.getBillType())) {
          oldItem = item;
          break;
        }
      }
    }


    int count = ((Number) stat[2]).intValue();
    double amount = ((Number) stat[3]).doubleValue();
    double cost = ((Number) stat[4]).doubleValue();
    double profit = ((Number) stat[5]).doubleValue();
    if (oldItem == null) {
      ReportStatSubItemDto item = new ReportStatSubItemDto();
      dto.getItems().add(item);

      item.setBillType(billType);
      item.setCount(count);
      item.setTotalAmount(amount);
      item.setTotalCost(cost);
      item.setTotalProfit(profit);
    } else {
      oldItem.setCount(count + oldItem.getCount());
      oldItem.setTotalAmount(amount + oldItem.getTotalAmount());
      oldItem.setTotalCost(cost + oldItem.getTotalCost());
      oldItem.setTotalProfit(profit + oldItem.getTotalProfit());
    }
  }

  @Override
  public List<ReportStatisticDto> statFlightMonthSaleByOp2(int enterpriseId, Date beginDate, Date endDate, List<Integer> allowedCustomerIds) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats = em.createQuery("select b.op2, b.billType," +
                    " count(b) as billCount, " +
                    "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit, b.customerId " +
                    "from BillInfo b " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate " +
                    " group by b.op2, b.billType, b.customerId " +
                    " order by b.op2, b.billType", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();

    String prevOp2 = null, op2 = null;

    ReportStatisticDto dto = null;

    for (Object[] stat : stats) {

      final int customerId = stat[6] == null ? 0 : (int) stat[6];
      if (allowedCustomerIds != null && !allowedCustomerIds.contains(customerId)) {
        continue;
      }

      op2 = (String) stat[0];
      if (op2 == null) {
        op2 = "";
      }

      if (!op2.equalsIgnoreCase(prevOp2)) {
        dto = new ReportStatisticDto();
      }
      processReportStatItem(1, op2, prevOp2, dto, stat, results);
      if (!op2.equalsIgnoreCase(prevOp2)) {
        prevOp2 = op2;
      }


    }

    return results;
  }


  /**
   * 统计销售员的业界
   */
  @Override
  public List<ReportStatisticDto> statMonthSaleBySalesman(final int enterpriseId, final Date beginDate, final Date endDate,
                                                          List<Integer> allowedCustomerIds) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats = em.createQuery("select b.customerId, b.customerCode, count(b) as billCount, " +
                    "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit " +
                    "from BillInfo b  " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate " +
                    " group by b.customerId, b.customerCode " +
                    " order by b.customerId, b.customerCode", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .getResultList();


    for (Object[] stat : stats) {
      String salesman = "nobody";

      ReportStatisticDto dto = new ReportStatisticDto();

      Integer customerId = (Integer) stat[0];

      if (allowedCustomerIds != null) {
        if (customerId == null || !allowedCustomerIds.contains(customerId)) {
          continue;
        }
      }

      if (customerId != null) {
        Customer customer = em.find(Customer.class, customerId);
        if (customer.getSalesStaff().length() > 0) {
          salesman = customer.getSalesStaff();
        }
        dto.setCustomer(CustomerDtoFactory.createByCustomer(customer));
      } else {
        //散客或企业散客
        // 为空，则通过customerCode查找客户信息
        String customerCode = (String) stat[1];
        if (customerCode != null) {
          List<Customer> list = em.createQuery("select o from Customer o where o.enterpriseId = :enterpriseId and o.customerCode = :customerCode", Customer.class)
                  .setParameter("enterpriseId", enterpriseId)
                  .setParameter("customerCode", customerCode)
                  .getResultList();
          if (list.size() > 0 && list.get(0).getSalesStaff().length() > 0) {
            salesman = list.get(0).getSalesStaff();
          }
          if (list.size() > 0) {
            dto.setCustomer(CustomerDtoFactory.createByCustomer(list.get(0)));
          }
        } else {
          //散客
          dto.setCustomer(CustomerDtoFactory.createByCustomer(null));
        }
      }

      dto.setSalesman(salesman);

      int count = ((Number) stat[2]).intValue();
      dto.setCount(count);

      double amount = ((Number) stat[3]).doubleValue();
      dto.setTotalAmount(amount);

      double cost = ((Number) stat[4]).doubleValue();
      dto.setTotalCost(cost);

      double profit = ((Number) stat[5]).doubleValue();
      dto.setTotalProfit(profit);

      results.add(dto);
    }

    return results;
  }

  @Override
  public List<ReportStatisticDto> statMonthSaleByAirlines(int enterpriseId, Date beginDate, Date endDate) {
    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats = em.createQuery("select b.carrier, b.customerId, count(b) as billCount, " +
                    "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit " +
                    "from BillInfo b  " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate and b.billType = :billType " +
                    " group by b.carrier, b.customerId " +
                    " order by b.carrier, b.customerId", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", beginDate)
            .setParameter("endDate", endDate)
            .setParameter("billType", BILL_TYPE_FLIGHT)
            .getResultList();


    for (Object[] stat : stats) {
      ReportStatisticDto dto = new ReportStatisticDto();

      dto.setCarrier((String) stat[0]);

      Integer customerId = (Integer) stat[1];
      if (customerId != null) {
        Customer customer = em.find(Customer.class, customerId);
        dto.setCustomer(CustomerDtoFactory.createByCustomer(customer));
      } else {
        dto.setCustomer(CustomerDtoFactory.createByCustomer(null));
      }

      int count = ((Number) stat[2]).intValue();
      dto.setCount(count);

      double amount = ((Number) stat[3]).doubleValue();
      dto.setTotalAmount(amount);

      if (stat[4] != null) {
        double cost = ((Number) stat[4]).doubleValue();
        dto.setTotalCost(cost);
      }

      double profit = ((Number) stat[5]).doubleValue();
      dto.setTotalProfit(profit);

      results.add(dto);
    }

    return results;
  }

  @Override
  public List<ReportStatisticDto> statDaily(final int enterpriseId, LocalDate beginDate,
                                            LocalDate endDate) {

    List<ReportStatisticDto> results = new ArrayList<>();

    List<Object[]> stats = em.createQuery("select b.opDate as opDate, b.customerId, " +
                    "sum(b.billSum) as totalAmount, sum(b.cost) as totalCost, sum(b.profit) as totalProfit " +
                    "from BillInfo b  " +
                    " where b.enterpriseId = :enterpriseId and b.opDate >= :beginDate and b.opDate <= :endDate " +
                    " group by b.opDate, b.customerId "
                    + " order by b.opDate", Object[].class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("beginDate", DateUtil.createDate(beginDate))
            .setParameter("endDate", DateUtil.createDate(endDate))
            .getResultList();


    for (Object[] stat : stats) {
      ReportStatisticDto dto = new ReportStatisticDto();

      dto.setOpDate((Date) stat[0]);
      if (stat[1] == null) {
        dto.setCustomer(CustomerDto.create(0, null, null));
      } else {
        dto.setCustomer(CustomerDto.create((int) stat[1], null, null));
      }

      dto.setTotalAmount((double) stat[2]);
      dto.setTotalCost((double) stat[3]);
      dto.setTotalProfit((double) stat[4]);

      results.add(dto);
    }

    return results;
  }
}
